package com.example.books2.service;

import com.example.books2.entity.Book;
import com.example.books2.entity.BorrowRecord;
import com.example.books2.entity.BackupRecord;
import com.example.books2.entity.BackupData;
import jakarta.persistence.EntityManager;
import jakarta.annotation.PostConstruct;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.jdbc.core.JdbcTemplate;

import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.text.SimpleDateFormat;
import java.io.File;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.concurrent.TimeUnit;
import java.time.ZoneId;

@Service
public class LibraryService {
    @Autowired
    private EntityManager entityManager;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static final String BACKUP_DIR = "backup";  // 备份文件存放目录
    private static final String MYSQL_PATH = "C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\";  // MySQL安装路径

    @PostConstruct
    public void init() {
        // 确保备份目录存在
        File backupDir = new File(BACKUP_DIR);
        if (!backupDir.exists()) {
            backupDir.mkdirs();
        }
    }
    
    @Transactional
    public boolean borrowBook(Long bookId, Long userId) {
        try {
            // 先设置变量
            entityManager.createNativeQuery("SET @success = 0").executeUpdate();
            
            // 调用存储过程
            entityManager.createNativeQuery("CALL borrow_book(:bookId, :userId, @success)")
                .setParameter("bookId", bookId)
                .setParameter("userId", userId)
                .executeUpdate();
            
            // 获取结果（MySQL返回1表示true，0表示false）
            Number result = (Number) entityManager
                .createNativeQuery("SELECT @success")
                .getSingleResult();
            
            return result != null && result.intValue() == 1;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    
    @Transactional
    public void returnBook(Long borrowRecordId) {
        // 查找借阅记录
        BorrowRecord record = entityManager.find(BorrowRecord.class, borrowRecordId);
        if (record == null) {
            throw new RuntimeException("借阅记录不存在");
        }
        
        if (record.isReturned()) {
            throw new RuntimeException("该图书已经归还");
        }
        
        // 更新借阅记录
        entityManager.createNativeQuery(
            "UPDATE borrow_records SET returned = TRUE, return_date = NOW() WHERE id = :id")
            .setParameter("id", borrowRecordId)
            .executeUpdate();
    }
    
    @Transactional
    public String backupDatabase(String description) {
        try {
            String timestamp = new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date());
            String fileName = "backup_" + timestamp + ".sql";
            String backupFile = new File(BACKUP_DIR, fileName).getAbsolutePath();
            
            // 使用 ProcessBuilder 来执行命令
            ProcessBuilder pb = new ProcessBuilder(
                MYSQL_PATH + "mysqldump.exe",
                "-u" + "root",
                "-p" + "lijiaye3669",
                "--routines",  // 包含存储过程
                "--triggers",  // 包含触发器
                "--add-drop-table",  // 添加 DROP TABLE 语句
                "--add-drop-trigger",  // 添加 DROP TRIGGER 语句
                "library_db"
            );
            
            // 重定向输出到文件
            pb.redirectOutput(new File(backupFile));
            
            Process process = pb.start();

            // 添加超时处理
            if (!process.waitFor(30, TimeUnit.SECONDS)) {
                process.destroyForcibly();
                throw new RuntimeException("备份操作超时");
            }

            int exitCode = process.exitValue();
            if (exitCode != 0) {
                throw new RuntimeException("备份失败，退出码：" + exitCode);
            }

            // 保存备份记录到数据库
            BackupData backupData = new BackupData();
            backupData.setDescription(description);
            backupData.setBackupDate(LocalDateTime.now());
            entityManager.persist(backupData);
            entityManager.flush();  // 确保立即保存

            return fileName;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("数据库备份失败: " + e.getMessage(), e);
        }
    }
    
    @Transactional
    public void restoreDatabase(Long backupId) {
        try {
            // 获取备份记录
            BackupData backupData = entityManager.find(BackupData.class, backupId);
            if (backupData == null) {
                throw new RuntimeException("备份记录不存在");
            }

            // 构建文件名
            String timestamp = new SimpleDateFormat("yyyyMMdd_HHmmss")
                .format(Date.from(backupData.getBackupDate().atZone(ZoneId.systemDefault()).toInstant()));
            String fileName = "backup_" + timestamp + ".sql";
            File backupFile = new File(BACKUP_DIR, fileName);

            if (!backupFile.exists()) {
                throw new RuntimeException("备份文件不存在：" + fileName);
            }

            // 先删除所有表和存储过程
            jdbcTemplate.execute("DROP PROCEDURE IF EXISTS borrow_book");
            jdbcTemplate.execute("DROP TRIGGER IF EXISTS after_return_book");
            jdbcTemplate.execute("DROP TABLE IF EXISTS borrow_records");
            jdbcTemplate.execute("DROP TABLE IF EXISTS books");
            jdbcTemplate.execute("DROP TABLE IF EXISTS users");

            // 使用 mysql 命令还原数据
            ProcessBuilder pb = new ProcessBuilder(
                MYSQL_PATH + "mysql.exe",
                "-u" + "root",
                "-p" + "lijiaye3669",
                "library_db"
            );
            
            pb.redirectInput(backupFile);
            Process process = pb.start();

            // 添加超时处理
            if (!process.waitFor(30, TimeUnit.SECONDS)) {
                process.destroyForcibly();
                throw new RuntimeException("还原操作超时");
            }

            int exitCode = process.exitValue();
            if (exitCode != 0) {
                throw new RuntimeException("还原失败，退出码：" + exitCode);
            }

            // 清除持久化上下文
            entityManager.clear();
            
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("数据库还原失败: " + e.getMessage(), e);
        }
    }

    @Transactional(readOnly = true)
    public List<Book> getAllBooks() {
        return entityManager.createQuery("SELECT b FROM Book b", Book.class)
                .getResultList();
    }

    @Transactional(readOnly = true)
    public List<BorrowRecord> getBorrowRecords() {
        return entityManager.createQuery("SELECT br FROM BorrowRecord br", BorrowRecord.class)
                .getResultList();
    }

    @Transactional(readOnly = true)
    public List<BackupRecord> getBackupRecords() {
        return entityManager.createQuery(
            "SELECT new com.example.books2.entity.BackupRecord(b.id, b.description, b.backupDate) FROM BackupData b", 
            BackupRecord.class)
            .getResultList();
    }

    // 命令行显示方法
    public void showAllBooks() {
        List<Book> books = getAllBooks();
        System.out.println("ID\t状态\t\t书名\t\t作者\t\tISBN");
        System.out.println("----------------------------------------------------");
        for (Book book : books) {
            System.out.printf("%d\t%s\t%s\t%s\t%s%n",
                book.getId(),
                book.isAvailable() ? "可借" : "已借出",
                book.getTitle(),
                book.getAuthor(),
                book.getIsbn()
            );
        }
    }

    public void showBorrowRecords() {
        List<BorrowRecord> records = getBorrowRecords();
        System.out.println("记录ID\t图书\t\t借阅人\t\t借阅时间\t\t状态");
        System.out.println("----------------------------------------------------");
        for (BorrowRecord record : records) {
            System.out.printf("%d\t%s\t%s\t%s\t%s%n",
                record.getId(),
                record.getBook().getTitle(),
                record.getUser().getName(),
                record.getBorrowDate(),
                record.isReturned() ? "已还" : "借出"
            );
        }
    }
} 